{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 作业3\n",
    "（将当前文件夹下的data文件夹中的2023-09-13.xlsx中温度的最高值、最低值和平均值提取出来，写入汇总.xlsx中。汇总.xlsx中包含字段为：日期，最低温度，最高温度，平均温度。)\n",
    "### 通义千问：\n",
    "在文件夹C:\\python作业下新建一个汇总.xlsx，在第一行分别写入日期，最高值，最低值，平均值，在第二行A列写入2023-09-13再将C:\\python作业\\data下2023-09-13.xlsx中温度的最高值，最低值和平均值提取出来，写入汇总.xlsx中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "最高温度: 65.0857540008251\n",
      "最低温度: 30.6205032936291\n",
      "平均温度: 49.07277585787494\n",
      "已成功创建文件 'C:\\python作业\\汇总.xlsx' 并写入了日期、最高值、最低值和平均值。\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from openpyxl import Workbook\n",
    "\n",
    "# 定义文件路径\n",
    "source_file_path = r'C:\\python作业\\data\\2023-09-13.xlsx'\n",
    "output_file_path = r'C:\\python作业\\汇总.xlsx'\n",
    "\n",
    "# 读取源Excel文件，默认读取第一个工作表\n",
    "try:\n",
    "    df = pd.read_excel(source_file_path)\n",
    "\n",
    "    # 假设温度数据在名为'Temperature'的列中，请根据实际情况调整列名\n",
    "    temperature_column = '温度'  # 替换为实际的温度列名\n",
    "\n",
    "    if temperature_column in df.columns:\n",
    "        # 计算温度数据的最高值、最低值和平均值\n",
    "        max_temperature = df[temperature_column].max()\n",
    "        min_temperature = df[temperature_column].min()\n",
    "        avg_temperature = df[temperature_column].mean()\n",
    "\n",
    "        # 打印计算结果\n",
    "        print(f\"最高温度: {max_temperature}\")\n",
    "        print(f\"最低温度: {min_temperature}\")\n",
    "        print(f\"平均温度: {avg_temperature}\")\n",
    "\n",
    "        # 创建一个新的工作簿并选择活动的工作表\n",
    "        wb = Workbook()\n",
    "        ws = wb.active\n",
    "\n",
    "        # 在第一行分别写入标题\n",
    "        ws.append(['日期', '最高值', '最低值', '平均值'])\n",
    "\n",
    "        # 在第二行A列写入指定日期，并将计算的结果写入第二行其他列\n",
    "        ws.cell(row=2, column=1, value='2023-09-13')\n",
    "        ws.cell(row=2, column=2, value=max_temperature)\n",
    "        ws.cell(row=2, column=3, value=min_temperature)\n",
    "        ws.cell(row=2, column=4, value=avg_temperature)\n",
    "\n",
    "        # 保存新的工作簿\n",
    "        wb.save(output_file_path)\n",
    "        print(f\"已成功创建文件 '{output_file_path}' 并写入了日期、最高值、最低值和平均值。\")\n",
    "    else:\n",
    "        print(f\"未找到名为'{temperature_column}'的温度数据列，请检查您的文件和列名。\")\n",
    "except FileNotFoundError:\n",
    "    print(f\"文件路径 '{source_file_path}' 不存在，请确认文件位置是否正确。\")\n",
    "except Exception as e:\n",
    "    print(f\"发生错误: {e}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
